CREATE TABLE roads_tmp AS
 SELECT wkb_geometry as the_geom, cfcc,
 fedirp ,fename ,fetype ,fedirs ,
 module,
 tlid,
 substring(cfcc from 1 for 1) as cfcc_1,
 substring(cfcc from 2 for 1) as cfcc_2,
 substring(cfcc from 3 for 1) as cfcc_3

FROM completechain
 WHERE substring(cfcc for 1) = 'A' or substring(cfcc for 1) = 'P';

-- shouldnt make any changes
 update roads_tmp set fedirp ='' where fedirp isnull;
 update roads_tmp set fename ='' where fename isnull;
 update roads_tmp set fetype ='' where fetype isnull;
 update roads_tmp set fedirs ='' where fedirs isnull;

--make a easy-to-label name
ALTER TABLE roads_tmp add column name text;
UPDATE roads_tmp SET name =trim( both ' ' from fedirp || ' ' || fename || ' ' || fetype || ' '|| fedirs) ;


-- lengths
ALTER TABLE roads_tmp add column length_m float8;
update roads_tmp set length_m =
    length2d_spheroid (the_geom, 'SPHEROID["GRS_1980",6378137,298.257222101]'  )
  ;



CREATE TABLE roads AS 
 SELECT the_geom, cfcc, cfcc_1,cfcc_2,cfcc_3,module, tlid, name, length_m
 FROM roads_tmp;

CREATE INDEX  roads_idx_module on roads (module);
CREATE INDEX  roads_idx_moduleid on roads (module,tlid);
CREATE INDEX  roads_idx_spatial on roads using gist (the_geom gist_geometry_ops);
VACUUM ANALYSE  roads;

INSERT INTO geometry_columns values ('','public','roads','the_geom',2,1,'GEOMETRY');

DROP TABLE  roads_tmp;

CREATE TABLE railroads_tmp AS
 SELECT wkb_geometry as the_geom, cfcc,
 fedirp ,fename ,fetype ,fedirs ,
 module,
 tlid,
 substring(cfcc from 1 for 1) as cfcc_1,
 substring(cfcc from 2 for 1) as cfcc_2,
 substring(cfcc from 3 for 1) as cfcc_3

FROM completechain
 WHERE substring(cfcc for 1) = 'B' ;

-- shouldnt make any changes
 update railroads_tmp set fedirp ='' where fedirp isnull;
 update railroads_tmp set fename ='' where fename isnull;
 update railroads_tmp set fetype ='' where fetype isnull;
 update railroads_tmp set fedirs ='' where fedirs isnull;

--make a easy-to-label name
ALTER TABLE railroads_tmp add column name text;
UPDATE railroads_tmp SET name =trim( both ' ' from fedirp || ' ' || fename || ' ' || fetype || ' '|| fedirs) ;


-- lengths
ALTER TABLE railroads_tmp add column length_m float8;
update railroads_tmp set length_m =
    length2d_spheroid (the_geom, 'SPHEROID["GRS_1980",6378137,298.257222101]'  )
  ;


CREATE TABLE railroads AS 
 SELECT the_geom, cfcc, cfcc_1,cfcc_2,cfcc_3,module, tlid, name, length_m
 FROM railroads_tmp;

CREATE INDEX  railroads_idx_module on railroads (module);
CREATE INDEX  railroads_idx_moduleid on railroads (module,tlid);
CREATE INDEX  railroads_idx_spatial on railroads using gist (the_geom gist_geometry_ops);
VACUUM ANALYSE  railroads;

INSERT INTO geometry_columns values ('','public','railroads','the_geom',2,1,'GEOMETRY');

DROP TABLE  railroads_tmp;

SELECT cfcc, count(*) FROM roads GROUP BY cfcc ORDER BY cfcc;
SELECT cfcc, count(*) FROM railroads GROUP BY cfcc ORDER BY cfcc;

ogc_fid | wkb_geometry |  module  |   tlid   | rtsq |          feat
---------+--------------+----------+----------+------+------------------------
   64198 |              | TGR01083 | 95250591 |    1 | {1250,452,593,363,306}
   64199 |              | TGR01083 | 95250591 |    2 | {1782,1784}

ogc_fid  | wkb_geometry |  module  |   tlid   | rtsq |          feat
---------+--------------+----------+----------+------+------------------------
   64198 |              | TGR01083 | 95250591 |    1 | {1250,452,593,363,306,1782,1784}

begin;
update altname set feat = feat||(select dm2.feat from altname dm2 where dm2.module=altname.module and altname.tlid = dm2.tlid and dm2.rtsq = 1)
  WHERE rtsq =2;
  
delete from altname
  WHERE rtsq =1
  AND module||tlid in (select module||tlid from altname where rtsq=2);
commit;

alter table altname add column altname1 text;
alter table altname add column altname2 text;
alter table altname add column altname3 text;
alter table altname add column altname4 text;
alter table altname add column altname5 text;
alter table altname add column altname6 text;
alter table altname add column altname7 text;
alter table altname add column altname8 text;
alter table altname add column altname9 text;

 -- (this is also done in major_roads, so dont do it twice)
--
 update featureids set fedirp ='' where fedirp isnull;
 update featureids set fename ='' where fename isnull;
 update featureids set fetype ='' where fetype isnull;
 update featureids set fedirs ='' where fedirs isnull;
 
alter table featureids add full_name text;
update featureids set  full_name= trim( both ' ' from fedirp || ' ' || fename || ' ' || fetype || ' '|| fedirs) ;
CREATE INDEX featids_indx_mod_feat on featureids (module,feat);
vacuum analyse featureids;
--
update altname 
   set altname9 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[9])
   where array_upper(feat,1) >=9;

update altname 
   set altname8 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[8])
   where array_upper(feat,1) >=8;

update altname 
   set altname7 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[7])
   where array_upper(feat,1) >=7;

update altname 
   set altname6 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[6])
   where array_upper(feat,1) >=6;

update altname 
   set altname5 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[5])
   where array_upper(feat,1) >=5;

update altname 
   set altname4 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[4])
   where array_upper(feat,1) >=4;

update altname 
   set altname3 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[3])
   where array_upper(feat,1) >=3;

update altname 
   set altname2 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[2])
   where array_upper(feat,1) >=2;

update altname 
   set altname1 = 
     (select full_name from featureids where featureids.module = altname.module and featureids.feat= altname.feat[1])
   where array_upper(feat,1) >=1;

--
--WARNING, this takes a few hours!
--
CREATE TABLE roads2 AS 
   SELECT roads.the_geom, roads.cfcc, roads.cfcc_1, roads.cfcc_2, roads.cfcc_3,
          roads.module, roads.tlid, roads.name, roads.length_m,
          altname1,altname2,altname3,altname4,altname5,altname6,altname7,altname8,altname9
   FROM 
   roads LEFT JOIN altname ON 
     (
         (roads.module = altname.module)
         AND 
         (roads.tlid = altname.tlid)
  );


alter table roads2 rename column the_geom to gen_full;
alter table roads2 add column gen_1 geometry;
update roads2 set gen_1 = simplify(gen_full,0.0001);

delete from geometry_columns where f_table_name = 'roads';
INSERT INTO geometry_columns values ('','public','roads','gen_full',2,1,'GEOMETRY');
INSERT INTO geometry_columns values ('','public','roads','gen_1',2,1,'GEOMETRY');

 alter table roads2 add primary key (module,tlid);
 create index "roads_idx_module" on roads2 (module);
 create index "roads_idx_moduleid" on roads2 (module,tlid);
create index "roads_idx_spatial" on roads2 using gist (gen_full gist_geometry_ops);
create index "roads_idx_spatia2l" on roads2 using gist (gen_1 gist_geometry_ops);
vacuum analyse roads2;
  

-- kill the olds roads table
DROP TABLE roads;

--replace
ALTER TABLE roads2 rename to roads;